package org.compiere.model;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;

import extend.org.compiere.hrm.TaxCompute;
import extend.org.compiere.hrm.TaxVariable;
import extend.org.compiere.hrm.TypePayment;

public class Mhrmbusinesstripline extends X_hrm_businesstrip_line {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -8512134949168607998L;
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (Mhrmbusinesstripline.class);

	public Mhrmbusinesstripline(Properties ctx, int hrm_businesstrip_line_ID,
			String trxName) {
		super(ctx, hrm_businesstrip_line_ID, trxName);
	}

	public Mhrmbusinesstripline(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
	}

	/**
	 * Search for entries in the business trip line
	 * 
	 * @param C_BPartner_ID
	 * @param hrm_businesstrip_ID
	 * @return  Model business trip line
	 */
	public static Mhrmbusinesstripline getBTripline(int C_BPartner_ID, Timestamp dt) {
		
		if (C_BPartner_ID == 0)
			return null;
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		Mhrmbusinesstripline hrmbtl = null;
			
		// 
		String sql_ = "SELECT * FROM HRM_BUSINESSTRIP_LINE \n " +
						"WHERE ISACTIVE='Y' \n " +
							"AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
							"AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
							"AND STARTDATE BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
							"AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";				
			try
			{
				pstmt = DB.prepareStatement(sql_,null);				
				rs = pstmt.executeQuery();	
				if(rs.next())
					hrmbtl = new Mhrmbusinesstripline(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			
		return hrmbtl;
		
	}
	
	/* 
	 */
	@Override
	protected boolean beforeSave(boolean newRecord) {
		
		super.beforeSave(newRecord);
		
		 /** Generate employee tax */
		TaxVariable taxcomp =  TaxCompute.GetTax(getC_BPartner_ID(), gethrm_total_report().doubleValue(), getStartDate(), TypePayment.BUSINESSTRIP);
		sethrm_IPN(new BigDecimal(taxcomp.getIPN()));
		sethrm_OPV(new BigDecimal(taxcomp.getOPV()));
		sethrm_CNalog(new BigDecimal(taxcomp.getCNALOG()));
		sethrm_COtch(new BigDecimal(taxcomp.getCOTCH()));
		
		return true;
	}
	
	public static BigDecimal getBTripSum(int C_BPartner_ID, Timestamp dt){
		
		if(C_BPartner_ID == 0)
    		return null;
				
		BigDecimal result = new BigDecimal(0);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		//
		String sql_ = "SELECT coalesce(ROUND(SUM(HRM_TOTAL)),0) FROM HRM_BUSINESSTRIP_LINE \n " +
						"WHERE ISACTIVE='Y' \n " +
							"AND AD_CLIENT_ID=" + Env.getAD_Client_ID(Env.getCtx()) + " \n " +
							"AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
							"AND STARTDATE BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
							"AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";						
		try
		{
			pstmt = DB.prepareStatement(sql_,null);			
			rs = pstmt.executeQuery();
			if(rs.next())
				return rs.getBigDecimal(1);		
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}

		return result;
	}

 public static Mhrmbusinesstripline getBTripRec(Properties ctx, int hrm_businesstrip_id, int C_BPartner_ID) {
	
	 
	 if (hrm_businesstrip_id == 0 || C_BPartner_ID == 0)
		 return null;	 
	 //
    PreparedStatement pstmt = null;
	ResultSet rs = null;
	Mhrmbusinesstripline result = new Mhrmbusinesstripline (ctx, 0, null);
		
	// 
	String sql_ = "SELECT * FROM hrm_businesstrip_line \n " +
			         " WHERE ISACTIVE='Y' \n " +
			         " AND AD_CLIENT_ID=" + Env.getAD_Client_ID(ctx) + " \n " +
			         " AND hrm_businesstrip_id=" + hrm_businesstrip_id + " \n " +
			         " AND C_BPARTNER_ID=" + C_BPartner_ID;
		try
		{
			pstmt = DB.prepareStatement(sql_,null);			
			rs = pstmt.executeQuery();	
			if(rs.next())
				result = new Mhrmbusinesstripline(ctx, rs, null);
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
	return result;	
  }
 
	private Mhrmbusinesstriplinelist[] linesList;
 
 	/**
	 * 	Get signatures of the order
	 * 	@param whereClause starting with AND
	 * 	@return lines
	 */
	private Mhrmbusinesstriplinelist[] getListTrip (String whereClause)
	{
		String whereClauseFinal = "hrm_businesstrip_line_ID=? ";
		if (whereClause != null)
			whereClauseFinal += whereClause;
		List<Mhrmbusinesstriplinelist> list = new Query(getCtx(), I_hrm_businesstrip_line_list.Table_Name, whereClauseFinal, get_TrxName())
										.setParameters(gethrm_businesstrip_line_ID())
										.list();
		return list.toArray(new Mhrmbusinesstriplinelist[list.size()]);
	}	//	getLinesSigned

	/**
	 * 	Get Order Lines signed
	 * 	@param requery
	 * 	@return lines
	 */
	public Mhrmbusinesstriplinelist[] getListTrip (boolean requery)
	{
		if (linesList == null || linesList.length == 0 || requery)
			linesList = getListTrip(null);
		set_TrxName(linesList, get_TrxName());
		return linesList;
	}	//	getLinesSigned

	/**
	 * 	Get Signed of HR Order
	 * 	@return lines
	 */
	public Mhrmbusinesstriplinelist[] getListTrip()
	{
		return getListTrip(false);
	}	//	getLinesSigned
 
}
